package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.FoodsDao;
import pojo.Categories;
import pojo.Food;
import util.DBUtils;

public class FoodsDaoImpl extends BaseDaoImpl<Food> implements FoodsDao {

    @Override
    public int add(Food food) {
        String sql = "insert into foods(id,name,price,image,c_id, m_id) values(?,?,?,?,?,?)";
        Connection con = null;
        PreparedStatement pst = null;
        con = DBUtils.createConnection();
        int m = 0;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, food.getId());
            pst.setString(2, food.getName());
            pst.setDouble(3, food.getPrice());
            pst.setString(4, food.getImage());
            pst.setString(5, food.getcId());
            pst.setString(6, food.getmId());
            m = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, null);
        }
        return m;
    }

    @Override
    public int delete(String id) {
        String sql = "DELETE FOODS WHERE ID = ?";
        Connection con = null;
        PreparedStatement pst = null;
        int m = 0;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, id);
            m = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, null);
        }
        return m;
    }

    @Override
    public int update(Food food) {
        String sql = "update foods set name=?,price=?,c_id=?,image=? where id=?";
        Connection con = null;
        PreparedStatement pst = null;
        int m = 0;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, food.getName());
            pst.setDouble(2, food.getPrice());
            pst.setString(3, food.getcId());
            pst.setString(4, food.getImage());
            pst.setString(5, food.getId());
            m = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, null);
        }
        return m;
    }

    @Override
    public Food load(String id) {
        String sql = "select * from foods where id =?";
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        Food food = null;
        try {
            con = DBUtils.createConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1, id);
            rs = pre.executeQuery();
            while (rs.next()) {
                food = new Food();
                food.setId(id);
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pre, rs);
        }
        return food;
    }

    @Override
    public List<Food> findAll() {
        List<Food> list = new ArrayList<Food>();
        String sql = "select * from foods";
        String sql1 = "select * from categories where id = ?";
        Connection con = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            Food food = null;
            Categories categories = null;
            while (rs.next()) {
                categories = new Categories();
                food = new Food();
                food.setId(rs.getString("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, rs.getString("c_id"));
                rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    categories.setId(rs1.getString("id"));
                    categories.setType(rs1.getString("type"));
                    food.setCategories(categories);
                }
                //food = new Food(id, name, price, image, cId,m_id);
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;
    }

    @Override
    public List<Food> findAllByMerchant(String mId) {
        List<Food> list = new ArrayList<Food>();
        String sql = "select * from foods where m_id = ?";
        String sql1 = "select * from categories where id = ?";
        Connection con = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, mId);
            rs = pst.executeQuery();
            Food food = null;
            Categories categories = null;
            while (rs.next()) {
                categories = new Categories();
                food = new Food();
                food.setId(rs.getString("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, rs.getString("c_id"));
                rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    categories.setId(rs1.getString("id"));
                    categories.setType(rs1.getString("type"));
                    food.setCategories(categories);
                }
                //food = new Food(id, name, price, image, cId,m_id);
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;
    }

    @Override
    public List<Food> findFoodByCategory(String mId, String typeId) {
        List<Food> list = new ArrayList<Food>();
        String sql = "select * from foods where c_id = ? and m_id = ?";
        String sql1 = "select * from categories where id = ?";
        Connection con = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, typeId);
            pst.setString(2, mId);
            rs = pst.executeQuery();
            Food food = null;
            Categories categories = null;
            while (rs.next()) {
                categories = new Categories();
                food = new Food();
                food.setId(rs.getString("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, typeId);
                rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    categories.setId(rs1.getString("id"));
                    categories.setType(rs1.getString("type"));
                    food.setCategories(categories);
                }
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;
    }

    @Override
    public List<Food> findByCid(String cId) {

        List<Food> list = new ArrayList<Food>();
        String sql = "select * from foods where c_id = ?";
        String sql1 = "select * from categories where id = ?";
        Connection con = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, cId);
            rs = pst.executeQuery();
            Food food = null;
            Categories categories = null;
            while (rs.next()) {
                categories = new Categories();
                food = new Food();
                food.setId(rs.getString("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, cId);
                rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    categories.setId(rs1.getString("id"));
                    categories.setType(rs1.getString("type"));
                    food.setCategories(categories);
                }
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;

    }


    public int getCountByMerchant(String mId) {
        String sql = "select count(0) as c from foods where m_id = ?";
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, mId);
            rs = pst.executeQuery();
            while (rs.next()) {
                count = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return count;
    }
    
    public int getCountByCategory(String mId,String typeId) {
        String sql = "select count(0) as c from foods where c_id = ? and m_id = ?";
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, typeId);
            pst.setString(2, mId);
            rs = pst.executeQuery();
            while (rs.next()) {
                count = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return count;
    }
    
    public List<Food> findAllByMerchant(String mId,int current, int pageSize) {
    	int begin = (current - 1) * pageSize + 1;
        int end = begin + pageSize - 1;
 
        List<Food> list = new ArrayList<Food>();
        String sql = "select * from foods where m_id = ?";
        String sql1 = "select * from categories where id = ?";
        String pageSql = "select * from(select rownum as rn, f.* from ("+sql+") f) temp where temp.rn between ? and ?";
        Connection con = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(pageSql);
            pst.setString(1, mId);
            pst.setInt(2, begin);
            pst.setInt(3, end);
            rs = pst.executeQuery();
            Food food = null;
            Categories categories = null;
            while (rs.next()) {
                categories = new Categories();
                food = new Food();
                food.setId(rs.getString("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, rs.getString("c_id"));
                rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    categories.setId(rs1.getString("id"));
                    categories.setType(rs1.getString("type"));
                    food.setCategories(categories);
                }
                //food = new Food(id, name, price, image, cId,m_id);
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;
    }

    @Override
    public List<Food> findFoodByCategory(String mId, String typeId,int current, int pageSize) {
    	int begin = (current - 1) * pageSize + 1;
        int end = begin + pageSize - 1;
    	
    	List<Food> list = new ArrayList<Food>();
        String sql = "select * from foods where c_id = ? and m_id = ?";
        String sql1 = "select * from categories where id = ?";
        String pageSql = "select * from(select rownum as rn, f.* from ("+sql+") f) temp where temp.rn between ? and ?";
        
        Connection con = null;
        PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        try {
            con = DBUtils.createConnection();
            pst = con.prepareStatement(pageSql);
            pst.setString(1, typeId);
            pst.setString(2, mId);
            pst.setInt(3, begin);
            pst.setInt(4, end);
            
            rs = pst.executeQuery();
            Food food = null;
            Categories categories = null;
            while (rs.next()) {
                categories = new Categories();
                food = new Food();
                food.setId(rs.getString("id"));
                food.setName(rs.getString("name"));
                food.setPrice(rs.getDouble("price"));
                food.setImage(rs.getString("image"));
                food.setcId(rs.getString("c_id"));
                food.setmId(rs.getString("m_id"));
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, typeId);
                rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    categories.setId(rs1.getString("id"));
                    categories.setType(rs1.getString("type"));
                    food.setCategories(categories);
                }
                list.add(food);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(con, pst, rs);
        }
        return list;
    }

}
